/*
 * ConceptualDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 */

package edu.byu.isys413.mystuff.sales;

import edu.byu.isys413.mystuff.ConnectionPool;
import edu.byu.isys413.mystuff.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Gabe Larsen
 */
public class ConceptualDAO extends ProductDAO{
    
    private static ConceptualDAO instance = null;
    
    /** Creates a new instance of ConceptualDAO */
    public ConceptualDAO() {
    }
    
    /**Retrieves an instance of the ConceputalDAO object**/
    public static synchronized ConceptualDAO getInstance() {
        if (instance == null) {
            instance = new ConceptualDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    public ConceptualBO create() throws Exception{
        String id = GUID.generate();
        ConceptualBO bo = new ConceptualBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getProductID(), bo);
        return bo;
    }
    
    
    public ConceptualBO read(String id) throws SQLException, DataException{
        Cache c = Cache.getInstance();
        ConceptualBO conceptual = (ConceptualBO)c.get(id);
        if (conceptual != null){
            return conceptual;
        }
        try {
            Connection conn = ConnectionPool.getInstance().get();
            conceptual = (ConceptualBO)read(id, conn);
            conn.commit();
            ConnectionPool.getInstance().release(conn);
        } catch (DataException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ConnectionPoolException ex) {
            ex.printStackTrace();
        }

            return conceptual;
    }//end read
    
    public synchronized ConceptualBO read(String id, Connection conn) throws SQLException, DataException{
        Cache c = Cache.getInstance();
        ConceptualBO conceptual = (ConceptualBO)c.get(id);
        if (conceptual != null){
            return conceptual;
        }
        conceptual = new ConceptualBO(id);
        c.put(id, conceptual);
        conceptual = (ConceptualBO)super.read(id, conn);
        
        PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Conceptual_Product WHERE ProductID =?");
        pstmt.setString(1,id);
        ResultSet rs = pstmt.executeQuery();
        
        if (rs.next()) {
            conceptual.setProductID(rs.getString("ProductID"));
            conceptual.setProductID(rs.getString("ProductName"));
            conceptual.setDescription(rs.getString("Description"));
            conceptual.setAverageCost(rs.getDouble("Average_Cost"));
            //conceptual.setManufacturer((ManufacturerBO)ManufacturerDAO.getInstance().read(rs.getString("manufacturer"), conn));
            //conceptual.setWarranty((WarrantyBO)WarrantyDAO.getInstance().read(rs.getString("warranty"), conn));
            c.put(conceptual.getProductID(), conceptual);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        return conceptual;
    }
    
    public synchronized ArrayList<ProductBO> readAll() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<ProductBO> list = new ArrayList<ProductBO>();
        String query = "SELECT * FROM Conceptual";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        while (rs.next()) {
            ConceptualBO conceptual = new ConceptualBO(rs.getString("ProductID"));
            Cache.getInstance().put(rs.getString("ProductID"), conceptual);
            conceptual = (ConceptualBO)super.read(rs.getString("ProductID"), conn);
            
            conceptual.setProductID(rs.getString("ProductID"));
            conceptual.setDescription(rs.getString("Description"));
            conceptual.setAverageCost(rs.getDouble("average_cost"));
            //conceptual.setManufacturer((ManufacturerBO)ManufacturerDAO.getInstance().read(rs.getString("manufacturer")));
            //conceptual.setWarranty((WarrantyBO)WarrantyDAO.getInstance().read(rs.getString("warranty")));
//            conceptual.setCategory(catDAO.read(rs.getString("category")));
            
            
            
            //conceptual.setAlreadyInDB(true);
            //conceptual.setDirty(false);
            
// save to the list
            list.add(conceptual);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    
    public synchronized ConceptualBO readBySKU(String xid) throws DataException, SQLException, ConnectionPoolException{
        
        String id;
        String query = "SELECT * FROM PRODUCT WHERE SKU = '" + xid + "'";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        if(rs.next()) {
            id = rs.getString("ProductID");
        }else{
            throw new DataException("Object was not found in the database.");
        }
        
        
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        ConceptualBO conceptual = (ConceptualBO)c.get(id);
        if (conceptual != null){
            return conceptual;                                                        // if so, return it immediately
        }
        //put it to the cache so the super.read can get it
        conceptual = new ConceptualBO(id);
        c.put(id, conceptual);
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        conceptual = (ConceptualBO)super.read(id, conn);
        
        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT * FROM Conceptual_Product WHERE ProductID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            
            conceptual.setProductID(rs.getString("ProductID"));
            conceptual.setDescription(rs.getString("Description"));
            conceptual.setAverageCost(rs.getDouble("average_cost"));
            //conceptual.setManufacturer((ManufacturerBO)ManufacturerDAO.getInstance().read(rs.getString("manufacturer")));
            //conceptual.setWarranty((WarrantyBO)WarrantyDAO.getInstance().read(rs.getString("warranty")));
//            conceptual.setCategory(catDAO.read(rs.getString("category")));
            
            
            // save to the cache
            c.put(conceptual.getProductID(), conceptual);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return conceptual;
    }
    
    
    protected void update(ConceptualBO bo, Connection conn) throws SQLException, DataException{
        //update the DB
        ConceptualBO BO = (ConceptualBO)bo;
        
//        super.save(BO, conn);
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Conceptual_Product SET ProductID = ?, " +
                "SET Description = ?, SET Average_Cost = ?, Warranty = ?, Manufacturer = ? WHERE ProductID = ?");
        
        pstmt.setString(1, BO.getProductID());
        pstmt.setString(2, BO.getDescription());
        pstmt.setDouble(3, BO.getAverageCost());
        //pstmt.setString(4, BO.getWarranty().getId());
        //pstmt.setString(5, BO.getManufacturer().getId());
        pstmt.setString(6, BO.getProductID());
        
        pstmt.executeUpdate();
        pstmt.close();
        //BO.setAlreadyInDB(true);
    }// end update
    
    public synchronized void insert(ProductBO bo, Connection conn) throws SQLException, DataException{
//update the DB
        ConceptualBO BO = (ConceptualBO)bo;
        
//        super.save(BO, conn);
        
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Conceptual_Product (PRODUCTID, description, average_cost, " +
                "warranty, manufacturer) VALUES (?, ?, ?, ?, ?)");
        
        pstmt.setString(1, BO.getProductID());
        pstmt.setString(2, BO.getDescription());
        pstmt.setDouble(3, BO.getAverageCost());
        //pstmt.setString(4, BO.getWarranty().getId());
        //pstmt.setString(5, BO.getManufacturer().getId());
        
        pstmt.executeUpdate();
        pstmt.close();
// tell the object that it's now in the db (so we call update next time not insert)
        //BO.setAlreadyInDB(true);
    }//end insert
}
